/**
 * 
 */
package indexer;

import helper.MysqlDatabase;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.CharacterIterator;
import java.text.StringCharacterIterator;

/**
 * @author Pascal de Vink
 * @version 0.2
 */
public class Db {

	private MysqlDatabase mdb;
	
	public Db()
	{
		this.mdb = new MysqlDatabase();
	}
	
	public void emptyFile() throws SQLException
	{
		this.mdb.empty("file");
	}
	
	public void emptyFolder() throws SQLException
	{
		this.mdb.empty("folder");
	}
	
	public void emptyTrack() throws SQLException
	{
		this.mdb.empty("track");
	}
	
	public void emptyArtist() throws SQLException
	{
		this.mdb.empty("artist");
	}
	
	public void emptyAlbum() throws SQLException
	{
		this.mdb.empty("album");
	}
	
	public void commitFile(String name, long size, int parent) throws SQLException
	{
		name = this.escapeString(name);
		String query = "INSERT INTO file(name, size, parent) VALUES('"+name+"',"+size+","+parent+")";
		//System.out.println(query);
		this.mdb.insert(query);
		//System.out.println("commiting file! ...." + name);
	}
	
	public void commitArtist(String name) throws SQLException
	{
		name = this.escapeString(name);
		String query = "INSERT IGNORE INTO artist(name) VALUES('"+name+"')";
		this.mdb.insert(query);
		//System.out.println("commiting Artist! ...." + name);
	}
	
	public void commitTrack(String title, int artist, int album, int file) throws SQLException
	{
		title = this.escapeString(title);
		String query = "INSERT INTO track(title, artist, album, file) VALUES('"+title+"', "+artist+", "+album+","+file+")";
		this.mdb.insert(query);
		//System.out.println("commiting Track! ...." + title);
	}
	
	public void commitAlbum(String title, int artist, int folder) throws SQLException
	{
		title = this.escapeString(title);
		String query = "INSERT IGNORE INTO album(title, artist, folder) VALUES('"+title+"', "+artist+", "+folder+")";
		this.mdb.insert(query);
		//System.out.println("commiting album! ...." + title);
	}
	
	public void commitFolder(String name, int parent) throws SQLException
	{
		name = this.escapeString(name);
		String query = "INSERT INTO folder(name, parent) VALUES('"+name+"',"+parent+")";
		this.mdb.insert(query);
		//System.out.println("commiting folder! ...." + name);
	}
	
	public int searchLastFolder(String name) throws SQLException
	{
		name = this.escapeString(name);
		String query = "SELECT id FROM folder WHERE name LIKE '"+name+"' ORDER BY id DESC LIMIT 0,1";
		ResultSet result = this.mdb.select(query);
		int id = 0;
		
		while(result.next())
		{
			id = result.getInt("id");
		}
		
		return id;
	}
	
	public int getFileID(String name) throws SQLException
	{
		name = this.escapeString(name);	
		String query = "SELECT id FROM file WHERE name LIKE '"+name+"' ORDER BY id DESC LIMIT 0,1";
		ResultSet result = this.mdb.select(query);
		int id = 0;
		
		while(result.next())
		{
			id = result.getInt("id");
		}
		
		return id;
	}
	
	public int getArtistID(String name) throws SQLException
	{
		name = this.escapeString(name);
		String query = "SELECT id FROM artist WHERE name LIKE '"+name+"' ORDER BY id DESC LIMIT 0,1";
		ResultSet result = this.mdb.select(query);
		int id = 0;
		
		while(result.next())
		{
			id = result.getInt("id");
		}
		
		return id;
	}
	
	public int getAlbumID(String title) throws SQLException
	{
		title = this.escapeString(title);
		String query = "SELECT id FROM album WHERE title LIKE '"+title+"' ORDER BY id DESC LIMIT 0,1";
		ResultSet result = this.mdb.select(query);
		int id = 0;
		
		while(result.next())
		{
			id = result.getInt("id");
		}
		
		return id;
	}
	
	public void closeDB()
	{
		this.mdb.closeDatabase();
	}
	
	public String escapeString(String aRegexFragment){
	    final StringBuffer result = new StringBuffer();

	    final StringCharacterIterator iterator = new StringCharacterIterator(aRegexFragment);
	    char character =  iterator.current();
	    while (character != CharacterIterator.DONE ){
	      /*
	      * All literals need to have backslashes doubled.
	      */
	      if (character == '.') {
	        result.append("\\.");
	      }
	      else if (character == '\\') {
	        result.append("\\\\");
	      }
	      else if (character == '?') {
	        result.append("\\?");
	      }
	      else if (character == '*') {
	        result.append("\\*");
	      }
	      else if (character == '+') {
	        result.append("\\+");
	      }
	      else if (character == '&') {
	        result.append("\\&");
	      }
	      else if (character == ':') {
	        result.append("\\:");
	      }
	      else if (character == '{') {
	        result.append("\\{");
	      }
	      else if (character == '}') {
	        result.append("\\}");
	      }
	      else if (character == '[') {
	        result.append("\\[");
	      }
	      else if (character == ']') {
	        result.append("\\]");
	      }
	      else if (character == '(') {
	        result.append("\\(");
	      }
	      else if (character == ')') {
	        result.append("\\)");
	      }
	      else if (character == '^') {
	        result.append("\\^");
	      }
	      else if (character == '$') {
	        result.append("\\$");
	      }
	      else if (character == '\'') {
		        result.append("\\'");
		      }
	      else if (character == '"') {
		        result.append("\\\"");
		      }
	      else {
	        //the char is not a special one
	        //add it to the result as is
	        result.append(character);
	      }
	      character = iterator.next();
	    }
	    return result.toString();
	  }
}
